<?php
/**
 * Created by PhpStorm.
 * User: LFL
 * Date: 2010/5/25
 * Time: 16:02
 */
namespace backend\controllers;

use backend\models\PurchaseOrder;
use Yii;
use yii\web\AdminController;
use backend\models\Warehouse;
use backend\models\Stock;
use backend\models\Supplier;
use PHPExcel;

class StockController extends AdminController
{
    public $layout = false;
    //业务关键字
    public $service = 'stock';

    //仓库列表
    public function actionIndex()
    {

        return $this->render('index');
    }

    //仓库列表
    public function actionGetWarehouseList()
    {
        $data = Yii::$app->request->post();
        $sql = '';
        $sql_list = 'select * from j_warehouse WHERE 1=1 ';
        $sql_count = 'SELECT COUNT(0) CNT from j_warehouse WHERE 1=1 ';

        if (isset($data['warehouseName']) && !empty($data['warehouseName'])) {
            $sql .= ' AND warehouseName like "%' . $data['warehouseName'] . '%"';
        }

        $list_count = Yii::$app->db->createCommand($sql_count . $sql)->queryOne();
        $count = $list_count['CNT'];
        $sql .= ' ORDER BY id desc';
        if ($data['start'] || $data['length']) {
            $sql .= ' limit ' . $data['start'] . ',' . $data['length'];
        }

        $list = Yii::$app->db->createCommand($sql_list . $sql)->queryAll();
        if (empty($list)) {
            $list = array();
        }

        //相关参数 http://datatables.club/manual/server-side.html
        $result['draw'] = intval($data['draw']);
        $result['recordsTotal'] = intval($count);
        $result['recordsFiltered'] = intval($count);
        $result['data'] = $list;
        //操作日志
        $this->saveLog($this->service, '供应商查询', 'VIEW', '获取物料列表' . "\r\n" . \yii\helpers\Json::encode($list));
        return \yii\helpers\Json::encode($result);
    }

    //仓库详情
    public function actionWarehouseDtl()
    {
        $data = Yii::$app->request->get();
        $warehouse = array();
        if ($data && isset($data['id'])) {
            $sql = 'select * from j_warehouse where id=' . $data['id'];
            $warehouse = Yii::$app->db->createCommand($sql)->queryOne();
        }

        $sql = 'select ProvinceID,ProvinceName from province';
        $province = Yii::$app->db->createCommand($sql)->queryAll();
        $sql = 'select CityID,CityName,ProvinceID from city';
        $cityRes = Yii::$app->db->createCommand($sql)->queryAll();
        $city = array();
        foreach ($cityRes as $temp) {
            $city[$temp['ProvinceID']][] = $temp;
        }

        return $this->render('wh-detail', ['warehouse' => $warehouse, 'province' => $province, 'city' => json_encode($city)]);
    }

    //修改仓库信息
    public function actionAddWarehouse()
    {
        $data = Yii::$app->request->post();

        $model = array();
        $model['warehouseName'] = $data['warehouseName'];
        $model['warehouseCode'] = $data['warehouseCode'];
        $model['storeType'] = $data['storeType'];
        $model['can_Frozen'] = $data['can_Frozen'];
        $model['province'] = $data['province'];
        $model['city'] = $data['city'];
        $model['area'] = $data['area'];
        $model['address'] = $data['address'];
        $model['contacts'] = $data['contacts'];
        $model['mobile'] = $data['mobile'];
        $model['phone'] = $data['phone'];
        $model['useFlg'] = $data['useFlg'];

        $transaction = Yii::$app->db->beginTransaction();
        try {

            if (empty($data['id'])) {
                $ckSql = "select count(0) cnt from j_warehouse where warehouseName='" . $data['warehouseName'] . "'";
                $ck_count = Yii::$app->db->createCommand($ckSql)->queryOne();
                if ($ck_count && $ck_count['cnt']) {
                    $this->retJson('仓库名称已存在', 500);
                }

                $model['creator'] = $this->getCreator();
                $model['createTime'] = date('Y-m-d H:i:s');
                $model['useFlg'] = 1;
                Yii::$app->db->createCommand()->insert('j_warehouse', $model)->execute();

            } else {
                $whId = $data['id'];
                $ckSql = "select count(0) cnt from j_warehouse where id<>" . $whId . " and warehouseName='" . $data['warehouseName'] . "'";
                $ck_count = Yii::$app->db->createCommand($ckSql)->queryOne();
                if ($ck_count && $ck_count['cnt']) {
                    $this->retJson('仓库名称已存在', 500);
                }
                Yii::$app->db->createCommand()->update('j_warehouse', $model, ['id' => $whId])->execute();

            }

            $transaction->commit();
            $this->retJson('操作成功', 200);
        } catch (\Exception $e) {
            $transaction->rollBack();
            error_log("异常:" . $e->getMessage() . $e->getLine());
            $this->retJson('操作失败', 500);
        }
    }

    //仓库门店组
    public function actionWhShopGroup()
    {
        $sql = 'select * from j_warehouse';
        $warehouse = Yii::$app->db->createCommand($sql)->queryAll();

        $sql = 'select * from j_shop';
        $shop = Yii::$app->db->createCommand($sql)->queryAll();

        return $this->render('wh-shop-group', ['warehouse' => $warehouse, 'shop' => $shop]);
    }

    //仓库门店组详情
    public function actionWhShopGroupDtl()
    {
        $data = Yii::$app->request->post();
        $sql = 'select * from j_warehouse_shop_group where id=' . $data['id'];
        $result = Yii::$app->db->createCommand($sql)->queryOne();
        $this->retJson('ok', 200, $result);
    }

    //仓库门店组数据
    public function actionWhShopGroupList()
    {
        $data = Yii::$app->request->post();
        $sql = '';
        $sql_list = 'select * from j_warehouse_shop_group WHERE 1=1 ';
        $sql_count = 'SELECT COUNT(0) CNT from j_warehouse_shop_group WHERE 1=1 ';

        if (isset($data['searchName']) && !empty($data['searchName'])) {
            $sql .= ' AND name like "%' . $data['searchName'] . '%"';
        }

        $list_count = Yii::$app->db->createCommand($sql_count . $sql)->queryOne();
        $count = $list_count['CNT'];
        $sql .= ' ORDER BY id desc';
        if ($data['start'] || $data['length']) {
            $sql .= ' limit ' . $data['start'] . ',' . $data['length'];
        }

        $list = Yii::$app->db->createCommand($sql_list . $sql)->queryAll();
        if ($list) {

            $sql = 'select id,warehouseName from j_warehouse';
            $whArr = Yii::$app->db->createCommand($sql)->queryAll();
            foreach ($whArr as $w) {
                $warehouse[$w['id']] = $w['warehouseName'];
            }

            $sql = 'select ShopID,ShopName from j_shop';
            $shopArr = Yii::$app->db->createCommand($sql)->queryAll();
            foreach ($shopArr as $s) {
                $shop[$s['ShopID']] = $s['ShopName'];
            }

            foreach ($list as &$dtl) {
                $warehouseName = '';
                $shopName = '';
                if (!empty($dtl['warehouseIds'])) {
                    $whIds = explode(',', $dtl['warehouseIds']);
                    foreach ($whIds as $ws) {
                        if ($warehouseName) $warehouseName .= ',';
                        $warehouseName .= $warehouse[$ws];
                    }
                }else{
                    $warehouseName = '全部';
                }
                if (!empty($dtl['shopIds'])) {
                    $shopIds = explode(',', $dtl['shopIds']);
                    foreach ($shopIds as $ss) {
                        if ($shopName) $shopName .= ',';
                        $shopName .= $shop[$ss];
                    }
                }else{
                    $shopName = '全部';
                }
                $dtl['warehouseName'] = $warehouseName;
                $dtl['shopName'] = $shopName;
            }
        } else {
            $list = array();
        }
        //相关参数 http://datatables.club/manual/server-side.html
        $result['draw'] = intval($data['draw']);
        $result['recordsTotal'] = intval($count);
        $result['recordsFiltered'] = intval($count);
        $result['data'] = $list;
        return \yii\helpers\Json::encode($result);
    }

    //仓库门店组数据
    public function actionAddWhShopGroup()
    {
        $data = Yii::$app->request->post();
        $model = array();
        $model['id'] = $data['id'];
        $model['name'] = $data['group_name'];
        $model['warehouseIds'] = $data['warehouse'] ? implode(',', $data['warehouse']) : '0';
        $model['shopIds'] = $data['shop'] ? implode(',', $data['shop']) : '0';
        $model['useFlg'] = $data['useFlg'];

        $transaction = Yii::$app->db->beginTransaction();
        try {

            if (empty($data['id'])) {
                $ckSql = "select count(0) cnt from j_warehouse_shop_group where name='" . $data['group_name'] . "'";
                $ck_count = Yii::$app->db->createCommand($ckSql)->queryOne();
                if ($ck_count && $ck_count['cnt']) {
                    $this->retJson('组名称已存在', 500);
                }

                $model['creator'] = $this->getCreator();
                $model['createTime'] = date('Y-m-d H:i:s');
                Yii::$app->db->createCommand()->insert('j_warehouse_shop_group', $model)->execute();

            } else {
                $ckSql = "select count(0) cnt from j_warehouse_shop_group where id<>" . $data['id'] . " and name='" . $data['group_name'] . "'";
                $ck_count = Yii::$app->db->createCommand($ckSql)->queryOne();
                if ($ck_count && $ck_count['cnt']) {
                    $this->retJson('组名称已存在', 500);
                }
                $model['editor'] = $this->getCreator();
                $model['editTime'] = date('Y-m-d H:i:s');
                Yii::$app->db->createCommand()->update('j_warehouse_shop_group', $model, ['id' => $data['id']])->execute();
            }

            $transaction->commit();
            $this->retJson('操作成功', 200);
        } catch (\Exception $e) {
            $transaction->rollBack();
            error_log("异常:" . $e->getMessage() . $e->getLine());
            $this->retJson('操作失败', 500);
        }
    }



    //入库单列表
    public function actionReceipt()
    {
        //仓库
        $warehouse_where = ['useFlg' => 1];
        $warehouse = Warehouse::find()->where($warehouse_where)->select('id,warehouseName')->asArray()->all();
        return $this->render('receipt', ['pageFlg' => 0, 'warehouse' => $warehouse]);
    }

    //损益单列表
    public function actionRevise()
    {
        $warehouse_where = ['useFlg' => 1];
        $warehouse = Warehouse::find()->where($warehouse_where)->select('id,warehouseName')->asArray()->all();
        return $this->render('receipt', ['pageFlg' => 1, 'warehouse' => $warehouse]);
    }

    //ajax入库单、损益单
    public function actionGetList()
    {
        $data = Yii::$app->request->post();
        $sql = '';
        $sql_list = 'SELECT st.*,wh.warehouseName FROM j_stock st INNER JOIN j_warehouse wh ON st.warehouseId=wh.id ';
        $sql_count = 'SELECT COUNT(0) CNT FROM j_stock st INNER JOIN j_warehouse wh ON st.warehouseId=wh.id ';

        $pageFlg = isset($data['pageFlg']) ? $data['pageFlg'] : 0;
        $sql .= ' WHERE sType = ' . $pageFlg;

        //searchName,stockStatus,warehouseId,
        //[remark] => 1111
        //    [expecteDate] => 2018-07-25 - 2018-07-25
        //    [actualDate] => 2018-07-25 - 2018-07-25


        if (isset($data['stockNo']) && !empty($data['stockNo'])) {
            $sql .= ' And st.stockNo like "%' . $data['stockNo'] . '%"';
        }
        if (isset($data['searchName']) && !empty($data['searchName'])) {
            $sql .= " AND EXISTS (select 1 from j_stock_dtl sd inner join j_materiel ma on sd.materielId=ma.id where ma.materielName like '%".$data['searchName']."%' and sd.stockNo = st.stockNo)";
        }
        if (isset($data['stockStatus']) && is_array($data['stockStatus'])) {
            $sql .= ' And st.stockStatus IN(' . implode(',', $data['stockStatus']) . ')';
        }
        if (isset($data['warehouseId']) && is_array($data['warehouseId'])) {
            $sql .= ' And st.warehouseId IN(' . implode(',', $data['warehouseId']) . ')';
        }
        if (isset($data['expecteDate']) && !empty($data['expecteDate'])) {
            $expecteDate = explode(' - ',$data['expecteDate']);
            $sql .= " AND st.expecteDate>='".$expecteDate[0]."' AND st.expecteDate <='".$expecteDate[1]." 23:59:59'";
        }
        if (isset($data['actualDate']) && !empty($data['actualDate'])) {
            $actualDate = explode(' - ',$data['actualDate']);
            $sql .= " AND st.actualDate>='".$actualDate[0]."' AND st.actualDate <='".$actualDate[1]." 23:59:59'";
        }
        if (isset($data['remark']) && !empty($data['remark'])) {
            $sql .= ' And st.remark like "%' . $data['remark'] . '%"';
        }

        $list_count = Yii::$app->db->createCommand($sql_count . $sql)->queryOne();
        $count = $list_count['CNT'];
        $sort_string = empty($data['columns'][$data['order'][0]['column']]['data']) ? 'st.id' : 'st.' . $data['columns'][$data['order'][0]['column']]['data'];
        $sql .= ' ORDER BY ' . $sort_string . ' ' . ($data['order'][0]['dir'] == 'asc' ? 'asc' : 'desc');
        if ($data['start'] || $data['length']) {
            $sql .= ' limit ' . $data['start'] . ',' . $data['length'];
        }

        $list = Yii::$app->db->createCommand($sql_list . $sql)->queryAll();
        if (empty($list)) {
            $list = array();
        }

        //相关参数 http://datatables.club/manual/server-side.html
        $result['draw'] = intval($data['draw']);
        $result['recordsTotal'] = intval($count);
        $result['recordsFiltered'] = intval($count);
        $result['data'] = $list;
        //操作日志
        $this->saveLog($this->service, '入库单查询', 'VIEW', '获取物料列表' . "\r\n" . \yii\helpers\Json::encode($list));
        return \yii\helpers\Json::encode($result);
    }

    //添加入库单||详情
    public function actionAddReceipt()
    {
        $data = Yii::$app->request->get();
        //仓库

        $pur = isset($data['pur']) ? 1 : 0;

        $warehouse_where = ['useFlg' => 1];
        $warehouse = Warehouse::find()->where($warehouse_where)->select('id,warehouseName')->asArray()->all();

        //供应商
        $supplier_where = ['useFlg' => 1];
        $supplier = Supplier::find()->where($supplier_where)->select('id,supplierName')->asArray()->all();

        $stock = null;
        if (isset($data['stockNo']) && !empty($data['stockNo'])) {
            $sql = "SELECT * FROM j_stock WHERE stockNo='" . $data['stockNo'] . "'";
            $stock = Yii::$app->db->createCommand($sql)->queryOne();
            if (!$stock) $this->error('未找到单据信息');
            $sql = "SELECT sd.*,ma.id as materielId,ma.materielName,ma.specDesc,ma.packDesc,ma.purchaseUnit,ma.orderUnit,
            ma.purchaseDay,ma.safeDay,ma.orderUnit,wh.warehouseName,sp.supplierName FROM j_stock_dtl sd
            INNER JOIN j_materiel ma ON sd.materielId=ma.id
            INNER JOIN j_supplier sp ON sd.supplierId=sp.id
            LEFT JOIN j_warehouse wh ON ma.warehouseId=wh.id WHERE sd.stockNo='" . $data['stockNo'] . "'";
            $stockDtl = Yii::$app->db->createCommand($sql)->queryAll();
            $stock['dtl'] = $stockDtl;

            if ($stock['purchaseOrderNo'] && !empty($stock['purchaseOrderNo'])) {
                $pur = 1;
            } else {
                $pur = 0;
            }

        }

        return $this->render('add-receipt', ['warehouse' => $warehouse, 'supplier' => $supplier, 'stock' => $stock, 'pur' => $pur]);
    }

    //添加损益单||详情
    public function actionAddRevise()
    {
        $data = Yii::$app->request->get();
        //仓库
        $warehouse_where = ['useFlg' => 1];
        $warehouse = Warehouse::find()->where($warehouse_where)->select('id,warehouseName')->asArray()->all();
        $stock = null;
        if (isset($data['stockNo']) && !empty($data['stockNo'])) {
            $sql = "SELECT * FROM j_stock WHERE stockNo='" . $data['stockNo'] . "'";
            $stock = Yii::$app->db->createCommand($sql)->queryOne();
            if (!$stock) $this->error('未找到单据信息');
            $sql = "SELECT ms.*,ma.id as materielId,ma.materielName,ma.specDesc,ma.packDesc,ma.purchaseUnit,ma.referPrice,
                    ma.orderUnit,wh.warehouseName,sp.supplierName,sd.stockId,sd.qty editQty,sd.dType 
                    FROM j_stock_dtl sd 
                    INNER JOIN j_materiel_stock ms ON ms.id=sd.stockId
                    INNER JOIN j_materiel ma ON sd.materielId=ma.id
                    INNER JOIN j_warehouse wh ON ms.warehouseId=wh.id
                    INNER JOIN j_supplier sp ON ms.supplierId=sp.id WHERE sd.stockNo='" . $data['stockNo'] . "'";
            $stockDtl = Yii::$app->db->createCommand($sql)->queryAll();
            $stock['dtl'] = $stockDtl;
        }
        return $this->render('add-revise', ['warehouse' => $warehouse, 'stock' => $stock]);
    }

    //ajax入库单-物料查询
    public function actionGetMaterielList()
    {
        $data = Yii::$app->request->post();

        $sql = '';
        $sql_list = 'SELECT ma.*,wh.warehouseName FROM j_materiel ma
        LEFT JOIN j_warehouse wh ON ma.warehouseId=wh.id WHERE 1=1';
        $sql_count = 'SELECT COUNT(0) CNT FROM j_materiel ma WHERE 1=1';

        $condition = $where = array();
        $searchKey = isset($data['searchKey']) ? trim($data['searchKey']) : '';
        //搜索关键字
        if (!empty($searchKey)) {
            $sql .= ' And (ma.materielName like "%' . $searchKey . '%"  OR ma.materielCode like "%' . $searchKey . '%")';
        }

        //所属仓库
        if (!empty($data['warehouseId'])) {
            $sql .= ' AND ma.warehouseId=' . $data['warehouseId'];
        }

        //是否管库存
        if (isset($data['storeFlg']) && !empty($data['storeFlg'])) {
            $sql .= ' AND ma.storeFlg=' . $data['storeFlg'];
        }

        if (isset($data['supplierId']) && !empty($data['supplierId'])) {
            $sql .= ' AND FIND_IN_SET(' . $data['supplierId'] . ',ma.supplierId)';
        }

        $sql .= ' And ma.useFlg = 1 ';

        $list_count = Yii::$app->db->createCommand($sql_count . $sql)->queryOne();
        $count = $list_count['CNT'];
        $sort_string = empty($data['columns'][$data['order'][0]['column']]['data']) ? 'ma.id' : 'ma.' . $data['columns'][$data['order'][0]['column']]['data'];
        $sql .= ' ORDER BY ' . $sort_string . ' ' . ($data['order'][0]['dir'] == 'asc' ? 'asc' : 'desc');
        if ($data['start'] || $data['length']) {
            $sql .= ' limit ' . $data['start'] . ',' . $data['length'];
        }

        $list = Yii::$app->db->createCommand($sql_list . $sql)->queryAll();
        if (!empty($list)) {
            //查询供应商
            foreach ($list as &$m) {
                $sql = 'SELECT id,supplierCode,supplierName FROM j_supplier WHERE id IN(' . $m['supplierId'] . ')';
                if (isset($data['supplierId']) && !empty($data['supplierId'])) {
                    $sql .= ' ORDER BY (CASE WHEN id=' . $data['supplierId'] . ' THEN 0 ELSE 1 END)';
                }
                $supplier = Yii::$app->db->createCommand($sql)->queryAll();
                $m['supplier'] = $supplier;
            }
        } else {
            $list = array();
        }

        //相关参数 http://datatables.club/manual/server-side.html
        $result['draw'] = intval($data['draw']);
        $result['recordsTotal'] = intval($count);
        $result['recordsFiltered'] = intval($count);
        $result['data'] = $list;
        //操作日志
        return \yii\helpers\Json::encode($result);
    }

    //ajax保存入库、损益单
    public function actionSaveStock()
    {
        $data = Yii::$app->request->post();

        if (!isset($data['materiels']) || !is_array($data['materiels']) || !isset($data['warehouseId'])) {
            $this->retJson('参数错误', 500);
        }

        $stock = array();
        $model = new Stock();
        $currentDate = date('Y-m-d H:i:s');
        $stock['warehouseId'] = $data['warehouseId'];
        $stock['purchaseOrderNo'] = isset($data['purchaseOrderNo']) && !empty($data['purchaseOrderNo']) ? $data['purchaseOrderNo'] : null;
        $stock['expecteDate'] = (isset($data['expecteDate']) && !empty($data['expecteDate'])) ? $data['expecteDate'] : null;
        $stock['remark'] = isset($data['remark']) ? $data['remark'] : '';
        $stock['sType'] = (isset($data['sType']) && in_array($data['sType'], array('0', '1'))) ? $data['sType'] : 0;

        $totalQty = 0;
        $totalAmount = 0;
        $stockDtl = array();

        if (isset($data['stockNo']) && !empty($data['stockNo'])) {
            $stockNo = $data['stockNo'];
        } else {
            $stockNo = ($data['sType'] == '0' ? 'R' : 'S') . date('YmdHis');
        }
        $stock['stockNo'] = $stockNo;

        if (isset($data['materiels']) && is_array($data['materiels'])) {

            foreach ($data['materiels'] as $m) {

                if ($data['sType'] == '0') {
                    /*
                     * 入库单 生成批次号
                     * 检查历史物料库存数据
                     * 无相同物料时直接使用 【生产日期+'001'】
                     * 有相同物料，生产日期、供应商、仓库 不同 【生产日期+'001'】
                     * 有相同物料，生产日期、供应商、仓库 相同，价格不同 max批次号递增+1
                     */
                    $sql = "SELECT sd.batch,sd.price,sd.supplierId,st.warehouseId
                            FROM j_stock_dtl sd INNER JOIN j_stock st ON sd.stockNo=st.stockNo
                            WHERE st.sType=0
                            AND sd.materielId='" . $m['materielId'] . "' 
                            AND sd.productDate='" . $m['productDate'] . "' ORDER BY sd.batch DESC";
                    $ck_stock = Yii::$app->db->createCommand($sql)->queryAll();

                    if ($ck_stock) {
                        $batch = '';
                        foreach ($ck_stock as $stock_dtl) {
                            if ($stock_dtl['supplierId'] == $m['supplierId'] &&
                                $stock_dtl['warehouseId'] == $data['warehouseId'] &&
                                $stock_dtl['price'] == $m['price']) {
                                $batch = $stock_dtl['batch'];
                                break;
                            }
                        }
                        if ($batch == '') $batch = $ck_stock[0]['batch'] + 1;

                    } else {
                        //新增批次
                        $batch = date('Ymd', strtotime($m['productDate'])) . '001';
                    }
                } else {
                    $batch = $m['batch'];
                }

                $stockDtl[] = array(
                    'creator' => $model->getCreator(),
                    'createTime' => $currentDate,
                    'stockNo' => $stockNo,
                    'stockId' => isset($m['stockId']) ? $m['stockId'] : null,
                    'materielId' => $m['materielId'],
                    'supplierId' => $m['supplierId'],
                    'batch' => $batch,
                    'productDate' => $m['productDate'],
                    'safeDate' => isset($m['safeDate']) ? $m['safeDate'] : date('Y-m-d', strtotime($m['productDate'] . ' + ' . $m['safeDay'] . 'day')),
                    'price' => $m['price'],
                    'dType' => isset($m['dType']) ? $m['dType'] : 0,
                    'qty' => $m['amount'],
                    'amount' => round($m['price'] * $m['amount'], 4),
                );
                $totalQty += $m['amount'];
                $totalAmount += round($m['price'] * $m['amount'], 4);
            }
        }

        $stock['totalQty'] = $totalQty;
        $stock['totalAmount'] = $totalAmount;

        try {
            $transaction = Yii::$app->db->beginTransaction();

            if (isset($data['stockNo']) && !empty($data['stockNo'])) {
                $stock['editor'] = $model->getCreator();
                $stock['editTime'] = $currentDate;
                $sql = "DELETE FROM j_stock_dtl WHERE stockNo='" . $data['stockNo'] . "'";
                Yii::$app->db->createCommand($sql)->execute();
                $model::updateAll($stock, "stockNo=:stockNo", array(':stockNo' => $stockNo));
            } else {
                $stock['creator'] = $model->getCreator();
                $stock['createTime'] = $currentDate;
                $stock['stockStatus'] = 0;
                Yii::$app->db->createCommand()->insert('j_stock', $stock)->execute();
            }
            foreach ($stockDtl as $dtl) {
                Yii::$app->db->createCommand()->insert('j_stock_dtl', $dtl)->execute();
            }

            $transaction->commit();
            $this->retJson('保存成功', 200);
        } catch (\Exception $e) {
            $transaction->rollBack();
            Yii::error($e->getMessage());
            $this->retJson('操作失败', 500);
        }

    }

    //ajax作废、审核
    public function actionEditStockStatus()
    {
        $data = Yii::$app->request->post();
        if (!isset($data['status']) || !isset($data['stockNo']) || !in_array($data['status'], array('1', '9')) || empty($data['stockNo'])) {
            $this->retJson('操作失败', 500);
        }

        $model = new Stock();
        $sql = "SELECT * FROM j_stock WHERE stockNo = '" . $data['stockNo'] . "'";
        $stock = Yii::$app->db->createCommand($sql)->queryOne();
        if ($stock && $stock['stockStatus'] != 0) {
            $this->retJson('单据状态异常，请刷新后重试', 500);
        }

        if($stock['purchaseOrderNo'] && !empty($stock['purchaseOrderNo'])){

            $sql = "SELECT status FROM j_purchase_order WHERE orderNo = '" . $stock['purchaseOrderNo'] . "'";
            $res = Yii::$app->db->createCommand($sql)->queryOne();
            if (!$res || $res['status'] != 1) {
                $this->retJson('采购订单['.$stock['purchaseOrderNo'].']已关闭', 500);
            }
        }


        $stock['stockStatus'] = $data['status'];
        $transaction = Yii::$app->db->beginTransaction();
        try {
            if ($data['status'] == 1) {
                $this->editStockAmount($stock['stockNo'], $stock['warehouseId']);
                $stock['reviewer'] = $model->getCreator();
                $stock['actualDate'] = date('Y-m-d');
            }
            $model::updateAll($stock, "stockNo=:stockNo", array(':stockNo' => $data['stockNo']));
            $transaction->commit();
            $this->retJson('操作成功', 200);
        } catch (\Exception $e) {
            $transaction->rollBack();
            error_log("异常:" . $e->getMessage() . $e->getLine());
            $this->retJson('操作失败', 500);
        }
    }

    //修改库存数量
    private function editStockAmount($stockNo, $warehouseId)
    {
        $model = new Stock();
        $sql = "SELECT * FROM j_stock_dtl WHERE stockNo = '" . $stockNo . "'";
        $dtlArr = Yii::$app->db->createCommand($sql)->queryAll();
        if ($dtlArr) {
            foreach ($dtlArr as $k => $dtl) {

                $stockId = $dtl['stockId'];
                if (!$stockId) {
                    $sql = "SELECT id FROM j_materiel_stock WHERE materielId='" . $dtl['materielId'] . "' AND batch='" . $dtl['batch'] . "'";
                    $materiel_stock = Yii::$app->db->createCommand($sql)->queryOne();
                    $stockId = $materiel_stock['id'];
                }

                if ($stockId) {
                    $qty = $dtl['qty'] * ($dtl['dType'] ? -1 : 1);
                    $sql = "UPDATE j_materiel_stock SET total=total+" . $qty . ", qty=qty+" . $qty . ",amount=amount+(" . $qty . "*price) WHERE id=" . $stockId;

                    Yii::$app->db->createCommand($sql)->execute();
                } else {
                    $tempStock = array(
                        'creator' => $model->getCreator(),
                        'createTime' => date('Y-m-d H:i:s'),
                        'materielId' => $dtl['materielId'],
                        'warehouseId' => $warehouseId,
                        'supplierId' => $dtl['supplierId'],
                        'batch' => $dtl['batch'],
                        'productDate' => $dtl['productDate'],
                        'safeDate' => $dtl['safeDate'],
                        'price' => $dtl['price'],
                        'total' => $dtl['qty'],
                        'freeze' => 0,
                        'qty' => $dtl['qty'],
                        'amount' => $dtl['qty'] * $dtl['price'],
                    );
                    Yii::$app->db->createCommand()->insert('j_materiel_stock', $tempStock)->execute();
                }
            }
        }
    }

    //ajax损益单-库存查询
    public function actionGetMaterielStock()
    {
        $data = Yii::$app->request->post();
        $sql_list = 'SELECT ms.*,ma.materielCode,ma.materielName,ma.specDesc,ma.packDesc,ma.purchaseUnit,ma.orderUnit,
                wh.warehouseName,sp.supplierName FROM j_materiel_stock ms
                INNER JOIN j_materiel ma ON  ms.materielId=ma.id
                INNER JOIN j_warehouse wh ON ms.warehouseId=wh.id
                INNER JOIN j_supplier sp ON ms.supplierId=sp.id WHERE 1=1 ';
        $sql_count = 'SELECT COUNT(0) CNT FROM j_materiel_stock ms
                INNER JOIN j_materiel ma ON  ms.materielId=ma.id
                INNER JOIN j_warehouse wh ON ms.warehouseId=wh.id
                INNER JOIN j_supplier sp ON ms.supplierId=sp.id WHERE 1=1 ';

        $sql = '';
        //仓库是否可用
        if (isset($data['can_Frozen']) && in_array($data['can_Frozen'],array('0','1'))) {
            $sql .= ' AND wh.can_Frozen=' . $data['can_Frozen'];
        }
        //搜索关键字
        if (isset($data['searchKey']) && !empty($data['searchKey'])) {
            $sql .= ' And (ma.materielCode like "%' . $data['searchKey'] . '%" OR ma.materielName like "%' . $data['searchKey'] . '%")';
        }
        if (isset($data['searchName']) && !empty($data['searchName'])) {
            $sql .= ' And ma.materielName like "%' . $data['searchName'] . '%"';
        }
        if (isset($data['searchCode']) && !empty($data['searchCode'])) {
            $sql .= ' And ma.materielCode like "%' . $data['searchCode'] . '%"';
        }
        //仓库
        if (isset($data['warehouseId']) && !empty($data['warehouseId'])) {
            $sql .= ' AND ms.warehouseId=' . $data['warehouseId'];
        }
        //供应商
        if (isset($data['supplierId']) && !empty($data['supplierId'])) {
            $sql .= ' AND ms.supplierId=' . $data['supplierId'];
        }
        //批次
        if (isset($data['searchBatch']) && !empty($data['searchBatch'])) {
            $sql .= ' And ms.batch = "' . $data['searchBatch'] . '"';
        }
        //生产日期
        if (isset($data['searchProDay']) && !empty($data['searchProDay'])) {
            $dayArr = explode(' - ', $data['searchProDay']);
            $sql .= ' And ms.productDate >= "' . $dayArr[0] . '" AND ms.productDate <="' . $dayArr[1] . ' 23:59:59"';
        }
        //到期日期
        if (isset($data['searchSafeDay']) && !empty($data['searchSafeDay'])) {
            $dayArr = explode(' - ', $data['searchSafeDay']);
            $sql .= ' And ms.safeDate >= "' . $dayArr[0] . '" AND ms.safeDate <="' . $dayArr[1] . ' 23:59:59"';
        }

        //编号精确查询
        if (isset($data['materielCode']) && !empty($data['materielCode'])) {
            $sql .= ' And ma.materielCode = "' . $data['materielCode'] . '"';
        }
        //编号精确查询
        if (isset($data['materielId']) && !empty($data['materielId'])) {
            $sql .= ' And ma.id = "' . $data['materielId'] . '"';
        }

        //可用数量>0
        if (isset($data['qtyFlg'])) {
            $sql .= ' And (ms.qty > 0 OR ms.freeze>0)';
        }

        $list_count = Yii::$app->db->createCommand($sql_count . $sql)->queryOne();
        $count = $list_count['CNT'];

        $sort_string = $sort = '';
        if (isset($data['order']) && isset($data['order'][0]['column'])) {
            $sort_string .= ',';
            $sort_string .= empty($data['columns'][$data['order'][0]['column']]['data']) ? 'ms.id' : 'ms.' . $data['columns'][$data['order'][0]['column']]['data'];
            $sort = $data['order'][0]['dir'] == 'asc' ? 'asc' : 'desc';
        } else {
            $sort = 'asc';
            $sort_string = ',ms.batch';
        }
        $sql .= ' ORDER BY ma.materielName ' . $sort_string . ' ' . $sort;
        if ($data['start'] || $data['length']) {
            $sql .= ' limit ' . $data['start'] . ',' . $data['length'];
        }
        $list = Yii::$app->db->createCommand($sql_list . $sql)->queryAll();
        if (empty($list)) {
            $list = array();
        }

        //相关参数 http://datatables.club/manual/server-side.html
        $result['draw'] = intval($data['draw']);
        $result['recordsTotal'] = intval($count);
        $result['recordsFiltered'] = intval($count);
        $result['data'] = $list;
        //操作日志
        return \yii\helpers\Json::encode($result);
    }

    //库存查询
    public function actionMaterielStock()
    {
        //供应商
        $supplier_where = ['useFlg' => 1];
        $supplier = Supplier::find()->where($supplier_where)->select('id,supplierName')->asArray()->all();

        $warehouse_where = ['useFlg' => 1];
        $warehouse = Warehouse::find()->where($warehouse_where)->select('id,warehouseName')->asArray()->all();
        return $this->render('materiel-stock', ['supplier' => $supplier, 'warehouse' => $warehouse]);
    }

    //库存转移
    public function actionMoveStock()
    {
        $data = Yii::$app->request->post();
        if (!isset($data['stockId']) || !isset($data['stockId']) ||
            !isset($data['toWhId']) || !isset($data['toWhId']) ||
            !isset($data['toQty']) || !isset($data['toQty'])) {
            $this->retJson('参数错误', 500);
        }

        $toQty = $data['toQty'];
        $model = new Stock();
        $ck_sql = "SELECT * FROM j_materiel_stock WHERE id = '" . $data['stockId'] . "'";
        $stock = Yii::$app->db->createCommand($ck_sql)->queryOne();
        if (!$stock) {
            $this->retJson('单据状态异常，请刷新后重试', 500);
        }
        if ($data['toQty'] > $stock['qty']) {
            $this->retJson('转出数量不可大于可用库存', 500);
        }

        $ck_sql = "SELECT toId FROM j_move_stock WHERE warehouseId='" . $data['toWhId'] . "' AND fromId = '" . $stock['id'] . "'";
        $ck_stock = Yii::$app->db->createCommand($ck_sql)->queryOne();

        $transaction = Yii::$app->db->beginTransaction();
        try {
            if ($ck_stock) {
                $toId = $ck_stock['toId'];
                $sql = 'UPDATE j_materiel_stock SET total=total+' . $toQty . ',qty=qty+' . $toQty . ',amount=amount+(price*' . $toQty . ') WHERE id=' . $toId;
                Yii::$app->db->createCommand($sql)->execute();
            } else {
                $batch = date('Ymd', strtotime($stock['productDate'])) . '1000';
                $b_sql = "SELECT MAX(batch) batch FROM j_materiel_stock
                          WHERE materielId='" . $stock['materielId'] . "' AND productDate='" . $stock['productDate'] . "' AND batch >='" . $batch . "'";
                $maxBatch = Yii::$app->db->createCommand($b_sql)->queryOne();
                if ($maxBatch && $maxBatch['batch']) {
                    $batch = $maxBatch['batch'] + 1;
                }
                $tempStock = array(
                    'creator' => $model->getCreator(),
                    'createTime' => date('Y-m-d H:i:s'),
                    'materielId' => $stock['materielId'],
                    'warehouseId' => $data['toWhId'],
                    'supplierId' => $stock['supplierId'],
                    'batch' => $batch,
                    'productDate' => $stock['productDate'],
                    'safeDate' => $stock['safeDate'],
                    'price' => $stock['price'],
                    'total' => $toQty,
                    'freeze' => 0,
                    'qty' => $toQty,
                    'amount' => $toQty * $stock['price'],
                );
                Yii::$app->db->createCommand()->insert('j_materiel_stock', $tempStock)->execute();
                $toId = Yii::$app->db->getLastInsertID();
            }

            $tempMove = array(
                'creator' => $model->getCreator(),
                'createTime' => date('Y-m-d H:i:s'),
                'fromId' => $stock['id'],
                'warehouseId' => $data['toWhId'],
                'toId' => $toId,
                'amount' => $toQty,
            );
            Yii::$app->db->createCommand()->insert('j_move_stock', $tempMove)->execute();

            //扣减库存
            $sql = 'UPDATE j_materiel_stock SET total=total-' . $toQty . ',qty=qty-' . $toQty . ',amount=amount-(price*' . $toQty . ') WHERE id=' . $stock['id'];
            Yii::$app->db->createCommand($sql)->execute();

            $transaction->commit();
            $this->retJson('操作成功', 200);
        } catch (\Exception $e) {
            $transaction->rollBack();
            error_log("异常:" . $e->getMessage() . $e->getLine());
            $this->retJson('操作失败', 500);
        }
    }

    //导出数据
    function actionExport()
    {
        $data = Yii::$app->request->get();
        $sql_list = 'SELECT ms.*,ma.materielName,ma.specDesc,ma.packDesc,ma.purchaseUnit,ma.orderUnit,
                wh.warehouseName,sp.supplierName FROM j_materiel_stock ms
                INNER JOIN j_materiel ma ON  ms.materielId=ma.id
                INNER JOIN j_warehouse wh ON ms.warehouseId=wh.id
                INNER JOIN j_supplier sp ON ms.supplierId=sp.id WHERE 1=1 ';

        $sql = '';
        //搜索关键字
        if (isset($data['searchName']) && !empty($data['searchName'])) {
            $sql .= ' And ma.materielName like "%' . $data['searchName'] . '%"';
        }
        //仓库
        if (isset($data['warehouseId']) && !empty($data['warehouseId'])) {
            $sql .= ' AND ms.warehouseId=' . $data['warehouseId'];
        }
        //供应商
        if (isset($data['supplierId']) && !empty($data['supplierId'])) {
            $sql .= ' AND ms.supplierId=' . $data['supplierId'];
        }
        //批次
        if (isset($data['searchBatch']) && !empty($data['searchBatch'])) {
            $sql .= ' And ms.batch = "' . $data['searchBatch'] . '"';
        }
        //生产日期
        if (isset($data['searchProDay']) && !empty($data['searchProDay'])) {
            $dayArr = explode(' - ', $data['searchProDay']);
            $sql .= ' And ms.productDate >= "' . $dayArr[0] . '" AND ms.productDate <="' . $dayArr[1] . ' 23:59:59"';
        }
        //到期日期
        if (isset($data['searchSafeDay']) && !empty($data['searchSafeDay'])) {
            $dayArr = explode(' - ', $data['searchSafeDay']);
            $sql .= ' And ms.safeDate >= "' . $dayArr[0] . '" AND ms.safeDate <="' . $dayArr[1] . ' 23:59:59"';
        }

        $list = Yii::$app->db->createCommand($sql_list . $sql)->queryAll();
        if (empty($list)) {
            $list = array();
        }

        $objPHPExcel = new PHPExcel();
        $objSheet = $objPHPExcel->getActiveSheet();//获取到sheet
        $objSheet->setTitle("demo");//设置sheet名字
        $objSheet->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置excel文件默认水平垂直方向居中
        $objSheet->getDefaultRowDimension()->setRowHeight(30);//设置默认行高
        $objSheet
            ->setCellValue("A1", '物料ID')
            ->setCellValue("B1", "物料名称")
            ->setCellValue("C1", "供应商")
            ->setCellValue("D1", "仓库")
            ->setCellValue("E1", "批次号")
            ->setCellValue("F1", "总数量")
            ->setCellValue("G1", "冻结数量")
            ->setCellValue("H1", "可用数量")
            ->setCellValue("I1", "单价")
            ->setCellValue("J1", "金额")
            ->setCellValue("K1", "生产日期")
            ->setCellValue("L1", "保质期")
            ->setCellValue("M1", "规格描述")
            ->setCellValue("N1", "包装方式")
            ->setCellValue("O1", "采购单位")
            ->setCellValue("P1", "订货单位");

        $i = 2;
        foreach ($list as $ma) {
            $objSheet
                ->setCellValue("A$i", $ma['materielId'])
                ->setCellValue("B$i", $ma['materielName'])
                ->setCellValue("C$i", $ma['supplierName'])
                ->setCellValue("D$i", $ma['warehouseName'])
                ->setCellValue("E$i", ''.$ma['batch'])
                ->setCellValue("F$i", ''.$ma['total'])
                ->setCellValue("G$i", ''.$ma['freeze'])
                ->setCellValue("H$i", ''.$ma['qty'])
                ->setCellValue("I$i", ''.$ma['price'])
                ->setCellValue("J$i", ''.$ma['amount'])
                ->setCellValue("K$i", $ma['productDate'])
                ->setCellValue("L$i", $ma['safeDate'])
                ->setCellValue("M$i", $ma['specDesc'])
                ->setCellValue("N$i", $ma['packDesc'])
                ->setCellValue("O$i", $ma['purchaseUnit'])
                ->setCellValue("P$i", $ma['orderUnit']);
            $i++;
        }

        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(12);

        $objSheet->getStyle("A1:P1")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('6fc144');//填充背景颜色
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');//生成excel文件 如：Excel2007
        $fileName = 'data-' . time() . '.xls';
        $this->browser_export('Excel5', $fileName);//输出到浏览器 设置游览器保存文件名
        $objWriter->save("php://output");
    }

    function browser_export($type,$filename)
    {
        if ($type == "Excel5") {
            header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件
        } else {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器数据excel07文件
        }
        header('Content-Disposition: attachment;filename="' . $filename . '"');//告诉浏览器将输出文件的名称
        header('Cache-Control: max-age=0');//禁止缓存
    }

    public function getCreator(){
        $userInfo = \common\helps\tools::getloginUserInfo();
        if($userInfo){
            $userName = $userInfo->userName;
        }else{
            $userName = '';
        }
        return $userName;
    }
}